﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace DBComparer.DataAccess
{
    public class Methods
    {

        public enum Type {
            SP,
            VW,
            UF
        }

        public SortedList<string, string> GetSP(SqlConnection argConnection, Type argType) 
        {
            string _type = "";

            switch (argType)
            {
                case Type.SP:
                    _type = "P";
                    break;
                case Type.VW:
                    _type = "V";
                    break;
                case Type.UF:
                    _type = "FN";
                    break;
                default:
                    break;
            }

            SqlDataAdapter adp = new SqlDataAdapter("SELECT name, text FROM sysobjects INNER JOIN syscomments ON sysobjects.id = syscomments.id WHERE sysobjects.type = @Type", argConnection);

            adp.SelectCommand.Parameters.Add("@Type", SqlDbType.VarChar, 10).Value = _type;

            DataSet ds = new DataSet();
            adp.Fill(ds);

            SortedList<string, string> sl = new SortedList<string, string>();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                if (!sl.Keys.Contains(dr["name"].ToString()))
                {
                    sl.Add(dr["name"].ToString(), StripText(dr["text"].ToString()));
                }
                else 
                {
                    // throw error!
                }
            }


            return sl;
        }

        private string StripText(string input)
        {
            input = Regex.Replace(input, @"/\*([^*]|[\r\n]|(\*+([^*/]|[\r\n])))*\*+/", ""); // Delete Comments 
            input = input.Replace(@"\\r\\n", "");
            input = input.ToLower(); // all chars to lower
            return input;
        }
    }
}
